MySQL provides native JSON functions to query and manipulate JSON data stored in JSON columns. Two commonly used functions are JSON_EXTRACT() and JSON_CONTAINS().

1. JSON_EXTRACT()

1
Retrieves data from a JSON document using a JSON path expression.
2
Returns the value at the specified path as JSON.
3
Can extract nested values, arrays, or objects.

JSON_EXTRACT() Example

2. JSON_CONTAINS()

1
Checks whether a specific JSON value exists within a JSON document.
2
Returns 1 if the value exists, 0 otherwise.
3
Supports optional path argument to search within nested structures.

JSON_CONTAINS() Example

3. Key Usage Notes

1
JSON_EXTRACT() is read-only and ideal for retrieving values from JSON columns.
2
JSON_CONTAINS() is useful in WHERE clauses to filter rows containing specific JSON data.
3
Both functions can work with indexes on generated columns to improve performance.
4
JSON paths use the $ symbol as the root and dot notation or array indices to traverse the structure.

In summary: Use JSON_EXTRACT() to retrieve specific values from JSON documents, and JSON_CONTAINS() to test whether certain values exist in JSON columns, enabling powerful filtering and querying of semi-structured data in MySQL.

Difficulty: 6/10

Topics: JSON extraction, JSON containment, MySQL indexing

Scenario Questions

0-2 years experience

1
We have a table orders with a JSON column details that stores an object with a field status. How would you write a query to return all rows where status equals 'shipped' using JSON_EXTRACT()?
2
Given a JSON column profile containing an array like ["admin","editor"], how can you check if the array contains the value 'admin' using JSON_CONTAINS()?

2-5 years experience

1
Our feature needs to filter users whose preferences JSON field includes a nested key notifications.email set to true. The current query using JSON_EXTRACT() returns no rows, but the data looks correct. Walk me through how you'd debug this and what might be wrong.
2
We decided to add a generated column that indexes the type field inside a JSON column for faster lookups. Explain how you'd use JSON_EXTRACT() in the generated column definition and discuss any limitations.

5-8 years experience

1
Our service stores large JSON payloads (up to 10KB) in MySQL and frequently runs queries with JSON_CONTAINS() to check for specific tags. What performance concerns arise, and how would you mitigate them at the schema or query level?
2
We need to migrate a legacy schema that stored key‑value pairs in separate columns to a single JSON column, but we still need to support existing queries that use JSON_EXTRACT(). How would you design a migration strategy that minimizes downtime and ensures backward compatibility?

8+ years experience

1
Looking ahead, we plan to shard our MySQL cluster and replicate data across regions, while heavily relying on JSON fields for flexible data models. What architectural considerations should we keep in mind regarding JSON_EXTRACT() and JSON_CONTAINS() usage, especially concerning index portability and query consistency?
2
Our organization wants to standardize JSON handling across multiple services, some using MySQL, others using PostgreSQL. How would you propose an abstraction layer or guidelines to ensure consistent behavior for extracting and checking JSON content, given differences in functions like JSON_EXTRACT() vs ->> operators?

Follow-up Questions

❓What index types does MySQL provide for JSON columns?
❓How does JSON_EXTRACT() differ from the -> operator?
❓How would you handle inconsistent JSON structures across rows?